﻿CREATE PROCEDURE [dbo].[proc_Employee_Getlist_WageJx]
	(
		@CompanyId int,
		@DepartmentId int,
		@CurMonth nvarchar(7),
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
begin
		declare @indextable table(id int identity(1,1),nid int)
		;WITH ChildDepartment(ID,DName,PId) AS
		(
			SELECT ID,DName,ParentID FROM department WHERE id=@DepartmentId
			UNION ALL
			SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
			INNER JOIN department ON d.ID = department.ParentID
		)
		insert into @indextable(nid) select Id from ChildDepartment;
	
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY e.Id DESC)AS Row,
	e.Id,
	e.UserName,
	e.EName,
	e.Sex,
	(Select CompanyName From Company Where Id=e.CompanyId) As CompanyName,
	(Select DName From Department Where Id=e.DepartmentId) As DepartmentName,
	isnull((Select Sum(TichengPrice) From Employee_WageJx Where Sales=e.UserName and Convert(nchar(7),FafangDate,120)=@CurMonth),0) as TichengPrice,
	isnull((Select Sum(JixiaoPrice) From Employee_WageJx Where Sales=e.UserName and Convert(nchar(7),FafangDate,120)=@CurMonth),0) as JixiaoPrice,
	isnull((Select Sum(JiangjinPrice) From Employee_WageJx Where Sales=e.UserName and Convert(nchar(7),FafangDate,120)=@CurMonth),0) as JiangjinPrice
	From Employee e
	Where (@CompanyId=0 Or e.CompanyId=@CompanyId) and (@DepartmentId=0 Or e.DepartmentId in (select c.nid from @indextable c))
	and (@Stext is null Or EName like '%'+@Stext+'%')
	)
	Select * From list Where Row between @StartIndex and @EndIndex
end
	RETURN
